【レンタル機器データの可視化】レンタル機器データを可視化してみた～データ準備編～
2024.09.03データ事業本部のsutoです。
BIチームでは、Amazon Athenaのテーブルに格納されたレンタル機器データの可視化をテーマにブログリレーを行っています。
今回は、レンタル機器データからAmazon S3に出力されたJSONデータをAmazon Athenaを使ってBIツールの可視化用テーブルを作成する手順を説明します。
Amazon Athenaとは
Amazon Athenaは、AWSが提供するサーバーレスのクエリサービスです。従来のデータウェアハウスソリューションとは異なり、Athenaではデータの保存場所であるデータレイクに対してSQL形式のクエリを実行することができます。料金も実際にクエリを実行したデータ量に基づいているため、試行的なデータ検索や加工などのアドホックなクエリ実行にとくに適しています。
基礎的な使い方はGetting Startのページをご参照ください。
また、さらにサービスの概要を知りたい方は以下の入門をご参照いただければと思います。
本シナリオにおけるデータの流れ
今回のシナリオにおけるレンタル機器のデータの流れは以下の図のイメージとなります。
レンタル機器のログをストリーミングデータとしてS3バケットへ出力し、基幹システムからS3バケットへ連携済みのデータと組み合わせてAmazon Athenaでデータ加工を行い、加工後のデータテーブルを使って各BIツールで可視化を実施する流れとなります。
今回は赤枠で囲んだ部分の解説となります。
可視化用テーブル作成
今回は、レンタル機器から取得されるデータはJSONデータであり、Amazon S3へ連携されている前提となります。
このJSONファイルをAthenaから読み込みテーブルを作成、さらにあらかじめcsvとしてS3に保存している基幹データ側のデータと組み合わせて加工し、可視化用のテーブルを作成する流れとなります。（データソースはS3上にcsvファイルとして保存するようにします。）
JSONファイル読み込み
まずはS3にある、以下のようなJSONファイル（一部抜粋）があります。
device.json
//レンタル開始時
{
"time": "2022-03-29 07:50:42",
"device_id": "AA01-xxxx-5",
"total_hours": "1047"
}
//レンタル終了時
{
"time": "2022-03-29 10:50:42",
"device_id": "AA01-xxxx-5",
"total_hours": "1050"
}
まずjsonファイルをもとにCREATE TABLE文で加工前テーブルを作成します。
CREATE EXTERNAL TABLE IF NOT EXISTS device_json (
time timestamp,
device_id string,
total_hours int
)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES ('ignore.malformed.json' = 'true' )
LOCATION 's3://xxxxxx/xxxx/json/';
これをもとに必要データの整理をしつつ、機器からの送信データのテーブルを作成したテーブルデータが以下となります。
また、上記のデバイスデータの他に「基幹システム管理データ」と「店舗リスト」のテーブルデータも準備します。
これらから作成した以下のデータマートが、今回のブログリレーの各BIツールで読み込むデータソースとなります。
CREATE TABLE bi_blog_relay2_mart
WITH (
external_location = 's3://xxxxxxxx/blog-relay-input-data/mart/',
format = 'TEXTFILE')
AS
select
core.store_id AS "店舗ID",
shop.store_name AS "店舗名",
shop.latitude AS "緯度",
shop.longitude AS "経度",
core.device_id AS "機器ID",
core.device_name AS "機器名",
core.model AS "機種",
core.device_number AS "機器管理番号",
core.start_time AS "管理開始日時",
core.exchange_times AS "交換基準(時間)",
device.total_uptime AS "累計稼働(時間)",
core.image AS "画像"
from bi_blog_relay2_core AS core
LEFT OUTER JOIN bi_blog_relay2_shop AS shop
ON
core.store_id = shop.store_id
LEFT OUTER JOIN bi_blog_relay2_device AS device
ON
core.device_number = device.device_number
;
最後に
レンタル機器データの可視化におけるデータ準備についてご紹介しました。
Amazon Athenaは、昔からJSONファイルからテーブル作成が可能でしたためにログ分析などに使われているサービスです。
すでにAWSのDWHサービスであるRedshiftもSUPER型で読み込むことで同じようにJSONデータを取り込むことが可能になっていますので、興味がある方はそちらもぜひご参考にしてみてください。
参考リンク
